﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Diagnostics;
using iTextSharp;
using iTextSharp.text.pdf;
using System.IO;
using iTextSharp.text;

class SalesReporter
{
    static void Main(string[] args)
    {
        SalesReporter rep = new SalesReporter();
        rep.MakePDFReport();

    }

    public void MakePDFReport()
    {
        string path = "";
        string format = "ddMMMyyyy";
        string date = DateTime.Now.ToString(format);
        Document salesReport = new Document();
        PdfWriter.GetInstance(salesReport, new FileStream(path + date + "_SalesReport.pdf", FileMode.Create));
        salesReport.Open();
        salesReport.Add(CreateTable());
        salesReport.Close();
    }


    static void AddEntries(PdfPTable table, ref decimal totalSum, string excelFile)
    {
        string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile
            + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = connectionString;

            using (DbCommand command = connection.CreateCommand())
            {

                command.CommandText = "SELECT * FROM [Sales$]";

                connection.Open();

                using (DbDataReader dr = command.ExecuteReader(System.Data.CommandBehavior.Default))
                {
                    dr.Read();
                    string location = dr[0].ToString();
                    dr.Read();

                    while (dr.Read() && dr[0].ToString()!="Total sum:")
                    {
                        int id = Int16.Parse(dr[0].ToString());
                        table.AddCell(SupermarketDAO.GetProductById(id));
                        table.AddCell(dr[1].ToString() + " " + SupermarketDAO.GetMeasureById(id));
                        table.AddCell(dr[2].ToString());
                        //     string location = String.Format("Supermarket - \"{0}\"", Path.GetFileNameWithoutExtension(excelFile));
                        table.AddCell(location);
                        table.AddCell(dr[3].ToString());
                        // totalSum += Double.Parse(dr[3].ToString());
                    }
                }
            }
        }
    }

    public PdfPTable CreateTable()
    {
        decimal totalSum = 0;
        PdfPTable table = new PdfPTable(5);
        table.SpacingBefore = 30f;

        table.SetWidths(new float[] { 1.3f, 1, 1, 3, 1 });



        table.HorizontalAlignment = 1;



        foreach (string label in new String[] { "Product", "Quantity", "Unit Price", "Location", "Sum" })
        {
            var phrase = new iTextSharp.text.Phrase(label);
            phrase.Font.SetStyle(Font.BOLD);
            PdfPCell labelCell = new PdfPCell(new iTextSharp.text.Phrase(label));

            labelCell.BackgroundColor = BaseColor.LIGHT_GRAY;
            
            table.AddCell(labelCell);

        }
        try
        {
            foreach (string excelFile in Directory.EnumerateFiles(".\\ExcelSheets\\", "*.xlsx"))
            {
                AddEntries(table, ref totalSum, excelFile);
            }
        }
        catch (IOException ioe)
        {
            Console.WriteLine(ioe);
            Console.ReadKey();
        }
        SupermarketDAO.SelectSales(table, ref totalSum);
        PdfPCell total = new PdfPCell(new iTextSharp.text.Phrase("Total sum:"));
        total.Colspan = 4;
        total.HorizontalAlignment = 2;
        table.AddCell(total);
        table.AddCell(totalSum.ToString("#######.00"));
        return table;
    }
}